﻿<%@ WebHandler Language="C#" Class="RBQueryAllFactoryStepShipPlanInfo" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using ReportBusiness;

/**
 * Description: 各分厂转工排产查询报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.4.14
 **/

public class RBQueryAllFactoryStepShipPlanInfo : IHttpHandler
{
    private uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getCompute":
                        r = getCompute(jObject);//统计数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getSearchSqlString(JObject jObject)
    {
        string sql = @"
SELECT f.factoryname fromfactory,cci.productname,cci.productdescription,cci.containername,cci.qty,
       cci.workflowstepname currentworkflowstepname,cci.fromworkcentername,
       wss.workflowstepname,f2.factoryname tofactory,
       csp.stepplancreatedate,csp.plannedstartdate,
       csd.senddate,
       csd.toacceptdate,
       csp.stepplanshipdate,csp.plannedfinishdate,
       csr.completedate,
       csr.recivedate,
       CASE WHEN csd.senddate IS NULL THEN 0 ELSE TRUNC(csd.senddate) - TRUNC(csp.plannedstartdate) END senddelaydays,
       CASE WHEN csp.plannedfinishdate IS NOT NULL 
            THEN 
                 CASE WHEN csr.completedate IS NOT NULL THEN TRUNC(csr.completedate) - TRUNC(csp.plannedfinishdate)
                      ELSE TRUNC(SYSDATE) - TRUNC(csp.plannedfinishdate)
                 END 
            ELSE 0 
       END finishdelaydays
FROM containerstepplan csp
LEFT JOIN containercurrentinfo cci ON cci.containerid = csp.containerid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid --主制分厂
LEFT JOIN workflowstep wss ON wss.workflowstepid = csp.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid = wss.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,wss.specid)
LEFT JOIN factory f2 ON f2.factoryid = s.factoryid --工序所属分厂
LEFT JOIN mesreport_containership_table sp ON csp.containerid = sp.containerid AND sp.workflowstepid = csp.workflowstepid
LEFT JOIN containershiprecord csd ON csd.containershiprecordid = sp.containershiprecordid
LEFT JOIN containershiprecive csr ON csr.containershiprecordid = sp.containershiprecordid
WHERE csp.plantype = 2 AND cci.containerstatus = 1 
";
        //发出分厂
        if (jObject["fromfactoryid"] != null && jObject["fromfactoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["fromfactoryid"].ToString());
        }

        //承制分厂
        if (jObject["tofactoryid"] != null && jObject["tofactoryid"].ToString() != "")
        {
            sql += string.Format(" AND f2.factoryid = '{0}'", jObject["tofactoryid"].ToString());
        }

        //零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        //接收日期
        if (jObject["plannedstartdate"] != null && jObject["plannedstartdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["plannedstartdate"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND TRUNC(csp.plannedstartdate) >= TO_DATE('{0}','yyyy-mm-dd') AND TRUNC(csp.plannedstartdate) <= TO_DATE('{1}','yyyy-mm-dd')", arr[0].ToString(), arr[1].ToString());
            }
        }
        return sql;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        Result r = new Result();

        string sql = getSearchSqlString(jObject);

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);

        DataTable dt = dto.DBTable;

        foreach (DataRow dr in dt.Rows)
        {
            if (dr["senddelaydays"].ToString() != "" && Convert.ToDouble(dr["senddelaydays"].ToString()) < 0)
            {
                dr["senddelaydays"] = 0;
            }
            if (dr["finishdelaydays"].ToString() != "" && Convert.ToDouble(dr["finishdelaydays"].ToString()) < 0)
            {
                dr["finishdelaydays"] = 0;
            }
        }

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getCompute(JObject jObject)
    {
        Result r = new Result();
        string sql = getSearchSqlString(jObject);
        DataTable dtRate = dal.GetDataTable(sql);

        foreach (DataRow dr in dtRate.Rows)
        {
            if (dr["senddelaydays"].ToString() != "" && Convert.ToDouble(dr["senddelaydays"].ToString()) < 0)
            {
                dr["senddelaydays"] = 0;
            }
            if (dr["finishdelaydays"].ToString() != "" && Convert.ToDouble(dr["finishdelaydays"].ToString()) < 0)
            {
                dr["finishdelaydays"] = 0;
            }
        }

        DataTable dtCountInfo = new DataTable();
        dtCountInfo.Columns.Add("productCount");//排产零件数
        dtCountInfo.Columns.Add("specCount");//排产工序数
        dtCountInfo.Columns.Add("finishSpecCount");//完工工序数
        dtCountInfo.Columns.Add("delayFinishSpecCount");//超期完工数
        dtCountInfo.Columns.Add("delaySpecCount");//超期工序数
        dtCountInfo.Columns.Add("notFinishSpecCount");//未完工序数
        dtCountInfo.Columns.Add("delaydaySum");//超期天数
        dtCountInfo.Columns.Add("finishRate");//完成率
        dtCountInfo.Columns.Add("intimeFinishRate");//按时完成率
        dtCountInfo.Columns.Add("delayFinishRate");//超期完成率

        dtCountInfo.Columns.Add("intimeFinishSpecCount");//按时完成数
        dtCountInfo.Columns.Add("notFinishRate");//未完成率

        dtCountInfo.Columns.Add("SendOutIntimeRate");//按时送达率
        dtCountInfo.Columns.Add("finishRateNew");//完成率（新规则） add zhunan 2019.3.7
        dtCountInfo.Columns.Add("notFinishRateNew");//未完成率（新规则） add zhunan 2019.3.7

        DataRow datarow = dtCountInfo.NewRow();
        dtCountInfo.Rows.Add(datarow);

        //计算排产零件数
        int productCount = dtRate.DefaultView.ToTable(true, "productname").Rows.Count;
        datarow["productCount"] = productCount;

        //计算排产工序数
        int specCount = dtRate.Rows.Count;
        datarow["specCount"] = specCount;

        //计算完工工序数
        int finishSpecCount = dtRate.Select(" completedate is not null ").Length;
        datarow["finishSpecCount"] = finishSpecCount;

        //计算超期完工数
        int delayFinishSpecCount = dtRate.Select(" completedate is not null and finishdelaydays <> 0 ").Length;
        datarow["delayFinishSpecCount"] = delayFinishSpecCount;

        //计算超期工序数
        int delaySpecCount = dtRate.Select(" finishdelaydays <> 0 ").Length;
        datarow["delaySpecCount"] = delaySpecCount;

        //计算未完工序数
        int notFinishSpecCount = dtRate.Select("completedate is null").Length;
        datarow["notFinishSpecCount"] = notFinishSpecCount;

        //计算超期天数
        decimal delaydaySum = 0;
        if (dtRate.Rows.Count > 0)
        {
            delaydaySum = Convert.ToDecimal(dtRate.Compute("Sum(finishdelaydays)", ""));
        }
        datarow["delaydaySum"] = delaydaySum;

        // 计算完成率
        if (specCount > 0)
        {
            decimal finishRate = ((decimal)finishSpecCount / specCount) * 100;// 完成率 = 完工工序数 / 工序总数
            datarow["finishRate"] = finishRate.ToString("0.00");
        }
        else
        {
            datarow["finishRate"] = "--";
        }

        if (specCount > 0)
        {
            decimal delayFinishRate = ((decimal)delayFinishSpecCount / specCount) * 100;// 超期完成率 = 超期完成率 / 工序总数
            datarow["delayFinishRate"] = delayFinishRate.ToString("0.00");

            decimal intimeFinishRate = ((decimal)(finishSpecCount - delayFinishSpecCount) / specCount) * 100;
            datarow["intimeFinishRate"] = intimeFinishRate.ToString("0.00");
        }
        else
        {
            datarow["delayFinishRate"] = "--";
            datarow["intimeFinishRate"] = "--";
        }

        //计算未完成率
        if (datarow["finishRate"].ToString() != "--")
        {
            datarow["notFinishRate"] = 100 - Convert.ToDecimal(datarow["finishRate"]);
        }

        //计算按时完成数
        datarow["intimeFinishSpecCount"] = Convert.ToInt32(datarow["finishSpecCount"]) - Convert.ToInt32(datarow["delayFinishSpecCount"]);

        //计算：实际发出时间、计划发出时间都不为空，且 实际发出时间<计划发出时间
        int SendOutRealQty = 0;
        DataRow[] drSendOutRealQty = dtRate.Select(" plannedstartdate is not null and senddate is not null and plannedfinishdate is not null ");
        if (drSendOutRealQty.Length > 0)
        {
            for (int i = 0; i < drSendOutRealQty.Length; i++)
            {
                DateTime plannedstartdate = Convert.ToDateTime(drSendOutRealQty[i]["plannedstartdate"]);
                DateTime senddate = Convert.ToDateTime(drSendOutRealQty[i]["senddate"].ToString().Split(' ')[0]);
                if (DateTime.Compare(plannedstartdate, senddate) >= 0)
                {
                    SendOutRealQty++;
                }
            }
        }

        //计算：机加分厂有计划发出时间，且有热表计划完成时间
        int SendOutQty = dtRate.Select(" plannedstartdate is not null and plannedfinishdate is not null ").Length;

        //计算按时送达率
        if (SendOutQty > 0)
        {
            //按时送达率 = SendOutRealQty / SendOutQty
            // SendOutRealQty: 实际发出时间、计划发出时间都不为空，且 实际发出时间<计划发出时间 (机加已排产并已经发出，且发出时间 <= 计划发出时间)
            // SendOutQty: 机加和热表都已排产
            decimal SendOutIntimeRate = ((decimal)SendOutRealQty / SendOutQty) * 100;
            datarow["SendOutIntimeRate"] = SendOutIntimeRate.ToString("0.00");// 按时送达率
        }
        else
        {
            datarow["SendOutIntimeRate"] = "--";
        }

        //按时完成率--分母（1.有计划发出日期、实际发出日期、计划完成日期。2.实际发出日期<计划发出日期）
        int finishRateNewFM = 0;

        DataRow[] drfinishRateNewFM = dtRate.Select(" plannedstartdate is not null and senddate is not null and plannedfinishdate is not null ");
        if (drfinishRateNewFM.Length > 0)
        {
            for (int i = 0; i < drfinishRateNewFM.Length; i++)
            {
                DateTime senddate = Convert.ToDateTime(drfinishRateNewFM[i]["senddate"].ToString().Split(' ')[0]);
                DateTime plannedstartdate = Convert.ToDateTime(drfinishRateNewFM[i]["plannedstartdate"]);

                if (DateTime.Compare(plannedstartdate, senddate) >= 0)
                {
                    finishRateNewFM++;
                }
            }
        }

        //按时完成率--分子（1.有计划发出日期、实际发出日期、计划完成日期、实际完成日期。2.实际发出时间 <= 计划发出时间。3.计划完成日期>实际完成日期。 ）
        int finishRateNewFZ = 0;
        DataRow[] drfinishRateNewFZ = dtRate.Select(" plannedstartdate is not null and senddate is not null and plannedfinishdate is not null and completedate is not null ");
        if (drfinishRateNewFZ.Length > 0)
        {
            for (int i = 0; i < drfinishRateNewFZ.Length; i++)
            {
                DateTime plannedfinishdate = Convert.ToDateTime(drfinishRateNewFZ[i]["plannedfinishdate"]);
                DateTime completedate = Convert.ToDateTime(drfinishRateNewFZ[i]["completedate"].ToString().Split(' ')[0]);

                DateTime senddate = Convert.ToDateTime(drfinishRateNewFZ[i]["senddate"].ToString().Split(' ')[0]);
                DateTime plannedstartdate = Convert.ToDateTime(drfinishRateNewFZ[i]["plannedstartdate"]);

                if (DateTime.Compare(plannedfinishdate, completedate) >= 0 && DateTime.Compare(plannedstartdate, senddate) >= 0)
                {
                    finishRateNewFZ++;
                }
            }
        }

        //计算按时完成率
        if (finishRateNewFM > 0)
        {
            // 按时完成率 = finishRateNewFZ / finishRateNewFM
            // finishRateNewFZ: (机加热表都已排产，且机加在计划发出时间之前发出，且热表在计划时间之内完成。)
            // finishRateNewFM: (机加热表都已排产，且机加在计划发出时间之前发出)
            decimal SendOutIntimeRate = ((decimal)finishRateNewFZ / finishRateNewFM) * 100;
            datarow["finishRateNew"] = SendOutIntimeRate.ToString("0.00");
        }
        else
        {
            datarow["finishRateNew"] = "--";
        }

        if (datarow["finishRateNew"].ToString() != "--")
        {
            datarow["notFinishRateNew"] = 100 - Convert.ToDecimal(datarow["finishRateNew"]);
        }
        else
        {
            datarow["notFinishRateNew"] = "--";
        }

        r.data = dtCountInfo;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        DataTable dtResult = dal.GetDataTable(sql);

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        foreach (DataRow dr in dtResult.Rows)
        {
            if (dr["senddelaydays"].ToString() != "" && Convert.ToDouble(dr["senddelaydays"].ToString()) < 0)
            {
                dr["senddelaydays"] = 0;
            }
            if (dr["finishdelaydays"].ToString() != "" && Convert.ToDouble(dr["finishdelaydays"].ToString()) < 0)
            {
                dr["finishdelaydays"] = 0;
            }
        }

        dtResult.Columns["fromfactory"].Caption = "主制分厂";
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["productdescription"].Caption = "零件名称";
        dtResult.Columns["containername"].Caption = "流水卡号";
        dtResult.Columns["qty"].Caption = "数量";
        dtResult.Columns["currentworkflowstepname"].Caption = "当前工序";
        dtResult.Columns["fromworkcentername"].Caption = "责任工区";
        dtResult.Columns["workflowstepname"].Caption = "排产工序";
        dtResult.Columns["tofactory"].Caption = "承制分厂";
        dtResult.Columns["stepplancreatedate"].Caption = "主制分厂排产时间";
        dtResult.Columns["plannedstartdate"].Caption = "计划发出时间";
        dtResult.Columns["senddate"].Caption = "实际发出时间";
        dtResult.Columns["stepplanshipdate"].Caption = "承制分厂排产时间";
        dtResult.Columns["plannedfinishdate"].Caption = "计划完成时间";
        dtResult.Columns["completedate"].Caption = "实际完成时间";
        dtResult.Columns["senddelaydays"].Caption = "发出超期时间";
        dtResult.Columns["finishdelaydays"].Caption = "完成超期时间";
        dtResult.Columns["recivedate"].Caption = "主制分厂转入时间";
        dtResult.Columns["toacceptdate"].Caption = "接收时间";
        ExportHelper.ExportExcel(dtResult, "各分厂转工排产查询报表");
    }
}